--    Program: 		CAPPOIMP_PERSON_ASSIGNMENT.sql
--    Description:  Import PO Person (Employee) Assignment information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Francis Lee
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_assignment_id			number;
   
   cursor cur_persons is
      select * from CRST_PER_ALL_ASSIGNMENTS_F
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_persons loop   	
       l_err_msg_tmp := '';
       
       select PER_ASSIGNMENTS_S.NEXTVAL
   	   into   l_assignment_id
   	   from   dual;
   	   
   	   update CRST_PER_ALL_ASSIGNMENTS_F
   	   set    assignment_id_n = l_assignment_id
   	   where  assignment_id = cur.assignment_id;
   	 
       begin
         -- import data
         insert into PER_ALL_ASSIGNMENTS_F
         (
           ASSIGNMENT_ID                   ,
           EFFECTIVE_START_DATE            ,
           EFFECTIVE_END_DATE              ,
           BUSINESS_GROUP_ID               ,
           RECRUITER_ID                    ,
           GRADE_ID                        ,
           POSITION_ID                     ,
           JOB_ID                          ,
           ASSIGNMENT_STATUS_TYPE_ID       ,
           PAYROLL_ID                      ,
           LOCATION_ID                     ,
           PERSON_REFERRED_BY_ID           ,
           SUPERVISOR_ID                   ,
           SPECIAL_CEILING_STEP_ID         ,
           PERSON_ID                       ,
           RECRUITMENT_ACTIVITY_ID         ,
           SOURCE_ORGANIZATION_ID          ,
           ORGANIZATION_ID                 ,
           PEOPLE_GROUP_ID                 ,
           SOFT_CODING_KEYFLEX_ID          ,
           VACANCY_ID                      ,
           PAY_BASIS_ID                    ,
           ASSIGNMENT_SEQUENCE             ,
           ASSIGNMENT_TYPE                 ,
           PRIMARY_FLAG                    ,
           APPLICATION_ID                  ,
           ASSIGNMENT_NUMBER               ,
           CHANGE_REASON                   ,
           COMMENT_ID                      ,
           DATE_PROBATION_END              ,
           DEFAULT_CODE_COMB_ID            ,
           EMPLOYMENT_CATEGORY             ,
           FREQUENCY                       ,
           INTERNAL_ADDRESS_LINE           ,
           MANAGER_FLAG                    ,
           NORMAL_HOURS                    ,
           PERF_REVIEW_PERIOD              ,
           PERF_REVIEW_PERIOD_FREQUENCY    ,
           PERIOD_OF_SERVICE_ID            ,
           PROBATION_PERIOD                ,
           PROBATION_UNIT                  ,
           SAL_REVIEW_PERIOD               ,
           SAL_REVIEW_PERIOD_FREQUENCY     ,
           SET_OF_BOOKS_ID                 ,
           SOURCE_TYPE                     ,
           TIME_NORMAL_FINISH              ,
           TIME_NORMAL_START               ,
           REQUEST_ID                      ,
           PROGRAM_APPLICATION_ID          ,
           PROGRAM_ID                      ,
           PROGRAM_UPDATE_DATE             ,
           ASS_ATTRIBUTE_CATEGORY          ,
           ASS_ATTRIBUTE1                  ,
           ASS_ATTRIBUTE2                  ,
           ASS_ATTRIBUTE3                  ,
           ASS_ATTRIBUTE4                  ,
           ASS_ATTRIBUTE5                  ,
           ASS_ATTRIBUTE6                  ,
           ASS_ATTRIBUTE7                  ,
           ASS_ATTRIBUTE8                  ,
           ASS_ATTRIBUTE9                  ,
           ASS_ATTRIBUTE10                 ,
           ASS_ATTRIBUTE11                 ,
           ASS_ATTRIBUTE12                 ,
           ASS_ATTRIBUTE13                 ,
           ASS_ATTRIBUTE14                 ,
           ASS_ATTRIBUTE15                 ,
           ASS_ATTRIBUTE16                 ,
           ASS_ATTRIBUTE17                 ,
           ASS_ATTRIBUTE18                 ,
           ASS_ATTRIBUTE19                 ,
           ASS_ATTRIBUTE20                 ,
           ASS_ATTRIBUTE21                 ,
           ASS_ATTRIBUTE22                 ,
           ASS_ATTRIBUTE23                 ,
           ASS_ATTRIBUTE24                 ,
           ASS_ATTRIBUTE25                 ,
           ASS_ATTRIBUTE26                 ,
           ASS_ATTRIBUTE27                 ,
           ASS_ATTRIBUTE28                 ,
           ASS_ATTRIBUTE29                 ,
           ASS_ATTRIBUTE30                 ,
           LAST_UPDATE_DATE                ,
           LAST_UPDATED_BY                 ,
           LAST_UPDATE_LOGIN               ,
           CREATED_BY                      ,
           CREATION_DATE                   ,
           TITLE                           ,
           OBJECT_VERSION_NUMBER           ,
           BARGAINING_UNIT_CODE            ,
           LABOUR_UNION_MEMBER_FLAG        ,
           HOURLY_SALARIED_CODE            ,
           CONTRACT_ID                     ,
           COLLECTIVE_AGREEMENT_ID         ,
           CAGR_ID_FLEX_NUM                ,
           CAGR_GRADE_DEF_ID               ,
           ESTABLISHMENT_ID                ,
           NOTICE_PERIOD                   ,
           NOTICE_PERIOD_UOM               ,
           EMPLOYEE_CATEGORY               ,
           WORK_AT_HOME                    ,
           JOB_POST_SOURCE_NAME            ,
           POSTING_CONTENT_ID              ,
           PERIOD_OF_PLACEMENT_DATE_START  ,
           VENDOR_ID                       ,
           VENDOR_EMPLOYEE_NUMBER          ,
           VENDOR_ASSIGNMENT_NUMBER        ,
           ASSIGNMENT_CATEGORY             ,
           PROJECT_TITLE                   ,
           APPLICANT_RANK
         )
         values
         (
           l_assignment_id                     ,
           cur.EFFECTIVE_START_DATE            ,
           cur.EFFECTIVE_END_DATE              ,
           CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),  
           cur.RECRUITER_ID                    ,
           cur.GRADE_ID                        ,
           CAPPOIMP_MAPPING.GetPositionIDMapping(cur.POSITION_ID),
           CAPPOIMP_MAPPING.GetJobIDMapping(cur.JOB_ID),
           cur.ASSIGNMENT_STATUS_TYPE_ID       ,
           cur.PAYROLL_ID                      ,
           CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.LOCATION_ID),
           cur.PERSON_REFERRED_BY_ID           ,
           CAPBRIMP_MAPPING.GetEmployeeMapping(cur.SUPERVISOR_ID),
           cur.SPECIAL_CEILING_STEP_ID         ,
           CAPBRIMP_MAPPING.GetEmployeeMapping(cur.PERSON_ID),
           cur.RECRUITMENT_ACTIVITY_ID         ,
           cur.SOURCE_ORGANIZATION_ID          ,
           CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
           cur.PEOPLE_GROUP_ID                 ,
           cur.SOFT_CODING_KEYFLEX_ID          ,
           cur.VACANCY_ID                      ,
           cur.PAY_BASIS_ID                    ,
           cur.ASSIGNMENT_SEQUENCE             ,
           cur.ASSIGNMENT_TYPE                 ,
           cur.PRIMARY_FLAG                    ,
           cur.APPLICATION_ID                  ,
           cur.ASSIGNMENT_NUMBER               ,
           cur.CHANGE_REASON                   ,
           cur.COMMENT_ID                      ,
           cur.DATE_PROBATION_END              ,
           cur.DEFAULT_CODE_COMB_ID            ,
           cur.EMPLOYMENT_CATEGORY             ,
           cur.FREQUENCY                       ,
           cur.INTERNAL_ADDRESS_LINE           ,
           cur.MANAGER_FLAG                    ,
           cur.NORMAL_HOURS                    ,
           cur.PERF_REVIEW_PERIOD              ,
           cur.PERF_REVIEW_PERIOD_FREQUENCY    ,
           cur.PERIOD_OF_SERVICE_ID            ,
           cur.PROBATION_PERIOD                ,
           cur.PROBATION_UNIT                  ,
           cur.SAL_REVIEW_PERIOD               ,
           cur.SAL_REVIEW_PERIOD_FREQUENCY     ,
           CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
           cur.SOURCE_TYPE                     ,
           cur.TIME_NORMAL_FINISH              ,
           cur.TIME_NORMAL_START               ,
           cur.REQUEST_ID                      ,
           cur.PROGRAM_APPLICATION_ID          ,
           cur.PROGRAM_ID                      ,
           cur.PROGRAM_UPDATE_DATE             ,
           cur.ASS_ATTRIBUTE_CATEGORY          ,
           cur.ASS_ATTRIBUTE1                  ,
           cur.ASS_ATTRIBUTE2                  ,
           cur.ASS_ATTRIBUTE3                  ,
           cur.ASS_ATTRIBUTE4                  ,
           cur.ASS_ATTRIBUTE5                  ,
           cur.ASS_ATTRIBUTE6                  ,
           cur.ASS_ATTRIBUTE7                  ,
           cur.ASS_ATTRIBUTE8                  ,
           cur.ASS_ATTRIBUTE9                  ,
           cur.ASS_ATTRIBUTE10                 ,
           cur.ASS_ATTRIBUTE11                 ,
           cur.ASS_ATTRIBUTE12                 ,
           cur.ASS_ATTRIBUTE13                 ,
           cur.ASS_ATTRIBUTE14                 ,
           cur.ASS_ATTRIBUTE15                 ,
           cur.ASS_ATTRIBUTE16                 ,
           cur.ASS_ATTRIBUTE17                 ,
           cur.ASS_ATTRIBUTE18                 ,
           cur.ASS_ATTRIBUTE19                 ,
           cur.ASS_ATTRIBUTE20                 ,
           cur.ASS_ATTRIBUTE21                 ,
           cur.ASS_ATTRIBUTE22                 ,
           cur.ASS_ATTRIBUTE23                 ,
           cur.ASS_ATTRIBUTE24                 ,
           cur.ASS_ATTRIBUTE25                 ,
           cur.ASS_ATTRIBUTE26                 ,
           cur.ASS_ATTRIBUTE27                 ,
           cur.ASS_ATTRIBUTE28                 ,
           cur.ASS_ATTRIBUTE29                 ,
           cur.ASS_ATTRIBUTE30                 ,
           cur.LAST_UPDATE_DATE                ,
           cur.LAST_UPDATED_BY                 ,
           cur.LAST_UPDATE_LOGIN               ,
           cur.CREATED_BY                      ,
           cur.CREATION_DATE                   ,
           cur.TITLE                           ,
           cur.OBJECT_VERSION_NUMBER           ,
           cur.BARGAINING_UNIT_CODE            ,
           cur.LABOUR_UNION_MEMBER_FLAG        ,
           cur.HOURLY_SALARIED_CODE            ,
           cur.CONTRACT_ID                     ,
           cur.COLLECTIVE_AGREEMENT_ID         ,
           cur.CAGR_ID_FLEX_NUM                ,
           cur.CAGR_GRADE_DEF_ID               ,
           cur.ESTABLISHMENT_ID                ,
           cur.NOTICE_PERIOD                   ,
           cur.NOTICE_PERIOD_UOM               ,
           cur.EMPLOYEE_CATEGORY               ,
           cur.WORK_AT_HOME                    ,
           cur.JOB_POST_SOURCE_NAME            ,
           cur.POSTING_CONTENT_ID              ,
           cur.PERIOD_OF_PLACEMENT_DATE_START  ,
           CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
           cur.VENDOR_EMPLOYEE_NUMBER          ,
           cur.VENDOR_ASSIGNMENT_NUMBER        ,
           cur.ASSIGNMENT_CATEGORY             ,
           cur.PROJECT_TITLE                   ,
           cur.APPLICANT_RANK  
         );  
       exception
         when others then
       	 l_err_msg_tmp := sqlerrm;
       end;	  	    
       
       if l_err_msg_tmp IS NULL then
         l_processed_count := l_processed_count + 1;  
       else
         l_rejected_count := l_rejected_count + 1;
         l_err_msg := l_err_msg_tmp; 
       end if;
    
   end loop;  

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PERSON_ASSIGNMENT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
